using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using Firestarter.DB.Schema.BO;
using Habanero.Base;
using Habanero.Base.Exceptions;

namespace Firestarter.DB.Schema.Logic
{
    /// <summary>
    /// Carries out the process of creating the DBSchema objects from the 
    /// Database based on the sepecified <see cref="IDBSchemaExtractor"/>
    /// where the IDBSchemaExtractor is specialised for the different database types.
    /// </summary>
    public class DatabaseReverseEngineerer
    {
        private IReverseEngineeringSettings ReverseEngSettings { get; set; }
        protected IDBSchemaExtractor SchemaExtractor { get; set; }
        protected DataTable _columnsDataTable;
        private DataTable _primaryKeyColumnsDataTable;
        private DataTable _uniqueKeyColumnsDataTable;
        private DataTable _foreignKeyColumnsDataTable;

        public DatabaseReverseEngineerer(IReverseEngineeringSettings reverseEngSettings, IDBSchemaExtractor dbSchemaExtractor)
        {
            if (reverseEngSettings == null) throw new ArgumentNullException("reverseEngSettings");
            if (dbSchemaExtractor == null) throw new ArgumentNullException("dbSchemaExtractor");
            ReverseEngSettings = reverseEngSettings;
            SchemaExtractor = dbSchemaExtractor;
        }

        /// <summary>
        /// Gets and sets the type converter that converts custom database types
        /// to a set of standards provided by default or by the user
        /// </summary>
        public DBTypeConverter DBTypeConverter { get; set; }
        /// <summary>
        /// Must the views be imported or not. This uses or sets the underlying <see cref="ReverseEngineeringSettings"/>
        /// </summary>
        public bool ImportViews
        {
            get { return this.ReverseEngSettings.ImportViews.GetValueOrDefault(); }
            set { this.ReverseEngSettings.ImportViews = value; }
        }

        private DataTable GetTablesDataTable()
        {
            return SchemaExtractor.GetTablesDataTable();
        }

        /// <summary>
        /// Creates a DBDatabase representing a Database defined in the Datasource (I.e. Returned by 
        /// the <see cref="DBSchemaExtractor.GetDatabasesDataTable(string)"/>).
        /// </summary>
        /// <param name="databaseName"></param>
        /// <returns></returns>
        public DBDatabase CreateDatabaseInfo(string databaseName)
        {
            DataTable dataTable = SchemaExtractor.GetDatabasesDataTable(databaseName);
            var dataRow = dataTable.Rows.Cast<DataRow>().FirstOrDefault();
            return CreateDBDatabase(databaseName, dataRow);
        }

        private static DBDatabase CreateDBDatabase(string databaseName, DataRow dataRow)
        {
            if (dataRow == null) return null;
            return new DBDatabase
                       {
                           Catalog = GetCatalogName(dataRow),
                           Schema = GetSchemaName(dataRow),
                           DatabaseName = databaseName
                       };
        }

        private static string GetSchemaName(DataRow dataRow)
        {
            return ((string) DBUtilities.ConvertDBNullToNull(dataRow[DBSchemaTemplate.Naming.SchemaName]));
        }

        private static string GetTableSchemaName(DataRow dataRow)
        {
            return ((string) DBUtilities.ConvertDBNullToNull(dataRow[DBSchemaTemplate.Naming.TableSchema]));
        }

        private static string GetCatalogName(DataRow dataRow)
        {
            return ((string) DBUtilities.ConvertDBNullToNull(dataRow[DBSchemaTemplate.Naming.CatalogName]));
        }
        /// <summary>
        /// Populates the dbDatabase with data from the database schema as determined by the 
        /// <see cref="IReverseEngineeringSettings"/> and the <see cref="IDBSchemaExtractor"/> 
        /// </summary>
        /// <param name="dbDatabase"></param>
        public void PopulateDBDatabase(DBDatabase dbDatabase)
        {
            try
            {
                if (dbDatabase == null) throw new ArgumentNullException("dbDatabase");
                PopulateTables(dbDatabase);
                //For Performance the DBReverseEngineering loads all columns etc and then
                // matches them to the Table later.
                _columnsDataTable = SchemaExtractor.GetColumnsDataTable(null);
                _primaryKeyColumnsDataTable = SchemaExtractor.GetPrimaryKeyColumnsDataTable(null);
                _uniqueKeyColumnsDataTable = SchemaExtractor.GetUniqueKeyColumnsDataTable(null);
                _foreignKeyColumnsDataTable = SchemaExtractor.GetForeignKeyColumnsDataTable(null, null);

                // foreach gets collection modification error
                foreach (DBTable dbTable in dbDatabase.Tables.ToArray())
                {
                    PopulateTable(dbTable);
                }
                foreach (DBView dbView in dbDatabase.Views.ToArray())
                {
                    PopulateView(dbView);
                }
                //The Relationships must be done last so as to ensure that all the 
                //Table with their properties and Unique constraints (Alternate Keys) are loaded.
                foreach (DBTable dbTable in dbDatabase.Tables.ToArray())
                {
                    PopulateForeignKeys(dbTable, _foreignKeyColumnsDataTable);
                }

            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateDBDatabase", "SchemaMerger");
            } 
        }
        /// <summary>
        /// Populates the <paramref name="dbDatabase"/> with the
        /// DBTables and DBViews extracted from the Schema.
        /// This applies filters etc as per the <see cref="ReverseEngineeringSettings"/>.
        /// </summary>
        /// <param name="dbDatabase"></param>
        public void PopulateTables(DBDatabase dbDatabase)
        {
            foreach (DataRow tableDataRow in GetTablesToBeImported())
            {
                try
                {
                    if (IsTable(tableDataRow))
                    {
                        DBTable dbTable = CreateDBTable(tableDataRow);
                        if (dbTable != null)
                        {
                            AddTable(dbTable, dbDatabase);
                        }
                    }
                    else
                    {
                        DBView dbView = CreateDBView(tableDataRow);
                        if (dbView != null)
                        {
                            AddView(dbView, dbDatabase);
                        }
                    }
                }
                catch (Exception ex)
                {
                    GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateTables", "SchemaMerger");
                }
            }
        }

        private void AddView(DBView dbView, DBDatabase dbDatabase)
        {
            DBView existingDBTable = dbDatabase.GetView(dbView.TableName);
            if (existingDBTable == null)
            {
                dbDatabase.Views.Add(dbView);
            }
        }

        private void AddTable(DBTable dbTable, DBDatabase dbDatabase)
        {
            DBTable existingDBTable = dbDatabase.GetTable(dbTable.TableName);
            if (existingDBTable == null)
            {
                dbDatabase.Tables.Add(dbTable);
            }
        }

        private IEnumerable<DataRow> GetTablesToBeImported()
        {
            DataTable tableList = GetTablesDataTable();
            return from DataRow tableDataRow in tableList.Rows
                   where MustBeImported(tableDataRow)
                   select tableDataRow;
        }

        private bool MustBeImported(DataRow tableInfoDataRow)
        {
            if (IsSystemsTable(tableInfoDataRow)) return false;
            if(IsView(tableInfoDataRow) && MustImportView()) return true;
            if (IsTable(tableInfoDataRow) && MustImportTable()) return true;
            return false;
        }

        private bool MustImportTable()
        {
            return ReverseEngSettings.ImportTables.GetValueOrDefault();
        }
        private bool MustImportView()
        {
            return ReverseEngSettings.ImportViews.GetValueOrDefault();
        }

        private static bool IsTable(DataRow tableInfoDataRow)
        {
            return GetTableType(tableInfoDataRow).ToUpper() == DBSchemaTemplate.Naming.TableTypeIsTable;
        }
        private static bool IsView(DataRow tableInfoDataRow)
        {
            return GetTableType(tableInfoDataRow).ToUpper() == DBSchemaTemplate.Naming.TableTypeIsView;
        }

        private static bool IsSystemsTable(DataRow tableInfoDataRow)
        {
            var tableName = GetTableName(tableInfoDataRow);
            return tableName == "sysdiagrams" || tableName == "dtproperties";
        }

        public void PopulateTable(DBTable dbTable)
        {
            try
            {
                if (dbTable == null) throw new ArgumentNullException("dbTable");
                if (dbTable.Database == null)
                    throw new NullReferenceException
                        ("You cannot generate Table Info since it does not have a reference to DBDatabase");

                PopulateColumns(dbTable, _columnsDataTable);
                PopulatePrimaryKeyInfos(dbTable, _primaryKeyColumnsDataTable);
                PopulateUniqueKeyInfos(dbTable, _uniqueKeyColumnsDataTable);

            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateTable", "SchemaMerger");
            } 
        }

        public void PopulateView(DBView dbView)
        {
            try
            {
                if (dbView == null) throw new ArgumentNullException("dbView");
                if (dbView.Database == null)
                    throw new NullReferenceException
                        ("You cannot generate View since it does not have a reference to DBDatabase");

                PopulateViewColumns(dbView, _columnsDataTable);

            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateView", "SchemaMerger");
            } 
        }

        public delegate void ProcessDataRowDelegate(DataRow dataRow, DBTable dbTable);
        private static void UpdateDBTable(DataTable dataTable, DBTable dbTable, ProcessDataRowDelegate rowProcessor)
        {
            DataTable dataTableSubSet = GetDataTableSubSet(dataTable, dbTable.TableName);
            foreach (DataRow columnInfoDataRow in dataTableSubSet.Rows)
            {
                try
                {
                    rowProcessor(columnInfoDataRow, dbTable);
                }
                catch (Exception ex)
                {
                    GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in UpdateDBTable", "SchemaMerger");
                } 
            }
        }

        //TODO brett 15 Sep 2010: This currently does not deal with two schemas having table or views with the
        // same name
        private static DataTable GetDataTableSubSet(DataTable dataTable, string tableName)
        {
            if (dataTable == null) return new DataTable();
            DataView dataView = new DataView(dataTable);
            string rowFilter;
            if (tableName == null)
            {
                rowFilter = string.Format("Isnull({0},'Null Column') = 'Null Column'", DBSchemaTemplate.Naming.TableName);
            }
            else
            {
                rowFilter = string.Format("{0} = '{1}'", DBSchemaTemplate.Naming.TableName, tableName);
            }
            dataView.RowFilter = rowFilter;
            dataTable = dataView.ToTable();
            return dataTable;
        }

        public void PopulateViewColumns(DBView dbView, DataTable dataTable)
        {
            DataTable dataTableSubSet = GetDataTableSubSet(dataTable, dbView.TableName);
            foreach (DataRow columnInfoDataRow in dataTableSubSet.Rows)
            {
                try
                {
                    CreateViewColumn(columnInfoDataRow, dbView);
                }
                catch (Exception ex)
                {
                    GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateViewColumns",
                                                              "SchemaMerger");
                }
            }
        }

        public void PopulateColumns(DBTable dbTable, DataTable dataTable)
        {
            try
            {
                UpdateDBTable(dataTable, dbTable, CreateDBColumn);
            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateColumns", "SchemaMerger");
            }           
        }

        public void PopulatePrimaryKeyInfos(DBTable dbTable, DataTable dataTable)
        {
            try
            {
                UpdateDBTable(dataTable, dbTable, CreatePrimaryKeyInfo);
            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulatePrimaryKeyInfos", "SchemaMerger");
            }
        }

        public void PopulateUniqueKeyInfos(DBTable dbTable, DataTable dataTable)
        {
            try
            {
/*                var rowTableUpdated = new UniqueKeyRowTableUpdate();
                rowTableUpdated.Update(dbTable, dataTable);*/
                UpdateDBTable(dataTable, dbTable, CreateAlternateKeyInfo);
            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateUniqueKeyInfos", "SchemaMerger");
            }
        }

        public void PopulateForeignKeys(DBTable dbTable, DataTable dataTable)
        {
            try
            {
                UpdateDBTable(dataTable, dbTable, CreateForeignKeyInfo);
            }
            catch (Exception ex)
            {
                GlobalRegistry.UIExceptionNotifier.Notify(ex, "Exception occurred in PopulateForeignKeys", "SchemaMerger");
            }
        }

        private static DBTable CreateDBTable(DataRow tableInfoDataRow)
        {
            var dbTable = new DBTable(GetTableName(tableInfoDataRow));
            dbTable.SchemaName = GetTableSchemaName(tableInfoDataRow);
            return dbTable;
        }

        private static DBView CreateDBView(DataRow tableInfoDataRow)
        {
            var dbView = new DBView(GetTableName(tableInfoDataRow));
            dbView.SchemaName = GetTableSchemaName(tableInfoDataRow);
            return dbView;
        }

        private static string GetTableType(DataRow tableInfoDataRow)
        {
            return ((string)DBUtilities.ConvertDBNullToNull(tableInfoDataRow[DBSchemaTemplate.Naming.TableType]));
        }

        private static string GetTableName(DataRow tableInfoDataRow)
        {
            return ((string)DBUtilities.ConvertDBNullToNull(tableInfoDataRow[DBSchemaTemplate.Naming.TableName]));
        }

        private void CreateDBColumn(DataRow columnInfoDataRow, DBTable dbTable)
        {
            string dataType = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.DataType]);
            string convertedDataType = ConvertDataType(dataType, DBTypeConverter);
            //TODO brett 25 Feb 2010: IS_Nullable does ot follow constant namimng rule
            DBColumn dbColumn = new DBColumn
            {
                ColumnName = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.ColumnName]),
                DataType = convertedDataType,
                Nullable = DBUtilities.ConvertToBoolean(columnInfoDataRow["IS_NULLABLE"]),
                DefaultValue = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.ColumnDefault]),
                DataLength = GetCharDataLength(columnInfoDataRow),
                OrdinalPosition = DBUtilities.ConvertDBToInt(columnInfoDataRow[DBSchemaTemplate.Naming.OrdinalPosition])
            };
            dbTable.Columns.Add(dbColumn);
        }

        private void CreateViewColumn(DataRow columnInfoDataRow, DBView dbView)
        {
            string dataType = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.DataType]);
            string convertedDataType = ConvertDataType(dataType, DBTypeConverter);
            //TODO brett 25 Feb 2010: IS_Nullable does ot follow constant namimng rule
            DBViewColumn dbColumn = new DBViewColumn
            {
                ColumnName = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.ColumnName]),
                DataType = convertedDataType,
                Nullable = DBUtilities.ConvertToBoolean(columnInfoDataRow["IS_NULLABLE"]),
                DefaultValue = DBUtilities.ConvertDBToString(columnInfoDataRow[DBSchemaTemplate.Naming.ColumnDefault]),
                DataLength = GetCharDataLength(columnInfoDataRow),
                OrdinalPosition = DBUtilities.ConvertDBToInt(columnInfoDataRow[DBSchemaTemplate.Naming.OrdinalPosition])
            };
            dbView.Columns.Add(dbColumn);
        }

        public static string ConvertDataType(string dataType, DBTypeConverter dbTypeConverter)
        {
            if (dbTypeConverter == null) return dataType;
            return dbTypeConverter.Convert(dataType);
        }

        internal static void CreatePrimaryKeyInfo(DataRow columnInfoDataRow, DBTable table)
        {
            var dbTable = table;
            if (dbTable == null) return;
            if (dbTable.PrimaryKeyDB == null) dbTable.PrimaryKeyDB = new DBPrimaryKey();
            string columnInfoName = (string)columnInfoDataRow[DBSchemaTemplate.Naming.ColumnName];
            DBColumn dbColumn = dbTable.GetColumn(columnInfoName);

            CheckColumnExists(dbTable, dbColumn);
            dbTable.PrimaryKeyDB.PrimaryKeyColumns.Add(new DBPrimaryKeyColumn { Column = dbColumn });
        }

        private static void CheckColumnExists(DBTable dbTable, DBColumn dbColumn)
        {
            if (dbColumn == null)
            {
                throw new HabaneroApplicationException
                    ("You cannot get the key for the table " + dbTable.TableName
                     + " since the tables columns have not been loaded yet");
            }
        }

        private static void CreateAlternateKeyInfo(DataRow alternateKeyRow, DBTable table)
        {
            var dbTable = table;
            if (dbTable == null) return;
            string indexName = (string) alternateKeyRow[DBSchemaTemplate.Naming.KeyName];
            string columnInfoName = (string) alternateKeyRow[DBSchemaTemplate.Naming.ColumnName];
            DBColumn dbColumn = dbTable.GetColumn(columnInfoName);

            CheckColumnExists(dbTable, dbColumn);

            DBKey existingDBKey = dbTable.GetKeyInfo(indexName);
            if (existingDBKey == null)
            {
                DBKey dbKey = new DBKey();
                dbKey.KeyColumns.Add(new DBKeyColumn { Column = dbColumn });
                dbKey.KeyName = indexName;
                dbTable.Keys.Add(dbKey);
            }
            else
            {
                existingDBKey.KeyColumns.Add(new DBKeyColumn { Column = dbColumn });
            }
        }

        private static void CreateForeignKeyInfo(DataRow row, DBTable table)
        {
            var dbTable = table;
            if (dbTable == null) return;
            string foreignKeyName = (string) row[DBSchemaTemplate.Naming.KeyName];
            DBForeignKey foundForeignKey = dbTable.GetForeignKeyInfo(foreignKeyName);
            
            string referencedTableName = (string) row[DBSchemaTemplate.Naming.ReferencedTableName];
            DBTable referencedDBTable = dbTable.Database.GetTable(referencedTableName);

            CheckReferencedTableExists(dbTable, foreignKeyName, referencedTableName, referencedDBTable);
            if (foundForeignKey == null)
            {
                foundForeignKey = new DBForeignKey {ForeignKeyName = foreignKeyName, RelatedTable = referencedDBTable};
                dbTable.ForeignKeys.Add(foundForeignKey);
            }
            
            DBForeignKeyColumn dbForeignKeyColumn = new DBForeignKeyColumn();
            string columnInfoName = (string)row[DBSchemaTemplate.Naming.ColumnName];
            DBColumn foundColumnInfo = dbTable.GetColumn(columnInfoName);
            dbForeignKeyColumn.ParentColumn = foundColumnInfo;
            string referencedColumnName = (string)row[DBSchemaTemplate.Naming.ReferencedColumnName];
            DBColumn relatedDBColumn = referencedDBTable.GetColumn(referencedColumnName);
            dbForeignKeyColumn.RelatedColumn = relatedDBColumn;
            foundForeignKey.ForeignKeyColumns.Add(dbForeignKeyColumn);
        }

        private static void CheckReferencedTableExists
            (DBTable dbTable, string foreignKeyName, string referencedTableName, DBTable referencedDBTable)
        {
            if (referencedDBTable == null)
            {
                throw new HabaneroApplicationException
                    (string.Format
                         ("You cannot Update the Foreign Key {0} for table {1} since the referenced table"
                          + " {2} does not exist in the schema", foreignKeyName, dbTable.TableName,
                          referencedTableName));
            }
        }

        public static int GetCharDataLength(DataRow row)
        {
            long dataLength = Convert.ToInt64(DBUtilities.ConvertDBNullToNull(row[DBSchemaTemplate.Naming.CharacterMaximumLength]));
            if (dataLength == 0)
            {
                string[] parts = GetColumnTypeParts(row, DBSchemaTemplate.Naming.DataType);
                if (parts.Length <= 1) parts = GetColumnTypeParts(row, "ColumnType");
                if (parts.Length > 1)
                {
                    string lengthInfo = parts[1];
                    lengthInfo = lengthInfo.Split(new[] {','})[0];
                    dataLength = Convert.ToInt64(lengthInfo);
                }
            }
            int intDataLength = dataLength > int.MaxValue ? int.MaxValue : Convert.ToInt32(dataLength);
            return intDataLength;
        }

        private static string[] GetColumnTypeParts(DataRow row, string columnName)
        {
            string[] parts = new string[] {};
            if (row.Table.Columns.Contains(columnName))
            {
                string columnType = Convert.ToString(DBUtilities.ConvertDBNullToNull(row[columnName]));
                parts = columnType.Split(new[] {'(', ')'}, StringSplitOptions.RemoveEmptyEntries);
            }
            return parts;
        }
    }
}
